﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace CruxCRUDBuilderHelper.DTOs
{
    public static class TableAttributes
    {
        public static List<FieldStructure> GetTableAttributes(string tableName)
        {
            List<FieldStructure> tblStructure = new List<FieldStructure>();
            string constraintType = string.Empty;
            SqlConnection con = new SqlConnection("Server=" + CodeAttribute.ServerIP + "; Database=" + AssemblyInfo.SelectedDatabase + "; uid=" + CodeAttribute.ServerUserName + "; pwd=" + CodeAttribute.ServerPassword);
            SqlCommand cmd = new SqlCommand("select column_name, column_default, is_nullable, data_type from INFORMATION_SCHEMA.COLUMNS where table_name='" + tableName + "'", con);
            cmd.CommandType = CommandType.Text;
            DataTable dtMainTable = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dtMainTable);
            DataTable dtPrimaryKeys = GetPrimaryKeys(tableName);
            DataTable dtForeignKeys = GetForeignKeys(tableName);
            DataTable dtIdentity = GetIdentityColumn(tableName);
            foreach (DataRow row in dtMainTable.Rows)
            {
                FieldStructure field = new FieldStructure();
                if (dtIdentity.Select("column_name like '" + row["column_name"] + "'").Count() > 0)
                    field.IsIdentity = true;
                if (dtPrimaryKeys.Select("column_name like '" + row["column_name"] + "'").Count() > 0)
                    constraintType = "P";
                else
                    if (dtForeignKeys.Select("Parent_column_name like '" + row["column_name"] + "'").Count() > 0)
                        constraintType = "F";
                    else
                        constraintType = String.Empty;
                field.Field = row["column_name"].ToString();
                field.DataType = row["data_type"].ToString();
                field.ConstraintType = constraintType;
                field.MaxLength = Convert.ToInt32(GetMaxLength(tableName, row["column_name"].ToString())["Max_Length"]);
                field.NullAble = row["is_nullable"].ToString().Equals("YES") ?
                    true : false;
                if (constraintType == "F")
                {
                    DataRow dr = dtForeignKeys.Select("Parent_column_name like '" + row["column_name"] + "'")[0];
                    field.RefTableName = dr["Referenced_table"].ToString();
                    field.RefColumnName = dr["Referenced_column_name"].ToString();
                    field.RefColumnNameField = GetRefColumnNameField(dr["Referenced_table"].ToString(), dr["Referenced_column_name"].ToString());
                }

                constraintType = string.Empty;

                tblStructure.Add(field);
            }
            return tblStructure;
        }

        private static DataRow GetMaxLength(string tableName, string colName)
        {
            SqlConnection con = new SqlConnection("Server=" + CodeAttribute.ServerIP + "; Database=" + AssemblyInfo.SelectedDatabase + "; uid=" + CodeAttribute.ServerUserName + "; pwd=" + CodeAttribute.ServerPassword);
            SqlCommand cmd = new SqlCommand("select distinct COL_LENGTH('" + tableName + "','" + colName + "') as Max_Length from INFORMATION_SCHEMA.COLUMNS", con);
            cmd.CommandType = CommandType.Text;
            DataTable dtMaxLength = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dtMaxLength);
            return dtMaxLength.Rows[0];
        }

        private static DataTable GetIdentityColumn(string tableName)
        {
            SqlConnection con = new SqlConnection("Server=" + CodeAttribute.ServerIP + "; Database=" + AssemblyInfo.SelectedDatabase + "; uid=" + CodeAttribute.ServerUserName + "; pwd=" + CodeAttribute.ServerPassword);
            SqlCommand cmd = new SqlCommand("select column_name from information_schema.columns where table_schema = 'dbo' and columnproperty(object_id(table_name), column_name,'IsIdentity') = 1 and table_name = '" + tableName + "'", con);
            cmd.CommandType = CommandType.Text;
            DataTable dtIdentity = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dtIdentity);
            return dtIdentity;
        }

        private static DataTable GetForeignKeys(string tableName)
        {
            SqlConnection con = new SqlConnection("Server=" + CodeAttribute.ServerIP + "; Database=" + AssemblyInfo.SelectedDatabase + "; uid=" + CodeAttribute.ServerUserName + "; pwd=" + CodeAttribute.ServerPassword);
            SqlCommand cmd = new SqlCommand("SELECT OBJECT_NAME(parent_object_id) 'Parent_table', c.NAME 'Parent_column_name',OBJECT_NAME(referenced_object_id) 'Referenced_table', cref.NAME 'Referenced_column_name' FROM  sys.foreign_key_columns fkc INNER JOIN sys.columns c ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.object_id INNER JOIN sys.columns cref ON fkc.referenced_column_id = cref.column_id AND fkc.referenced_object_id = cref.object_id where OBJECT_NAME(parent_object_id)='" + tableName + "'", con);
            cmd.CommandType = CommandType.Text;
            DataTable dtFKey = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dtFKey);
            return dtFKey;
        }

        private static DataTable GetPrimaryKeys(string tableName)
        {
            SqlConnection con = new SqlConnection("Server=" + CodeAttribute.ServerIP + "; Database=" + AssemblyInfo.SelectedDatabase + "; uid=" + CodeAttribute.ServerUserName + "; pwd=" + CodeAttribute.ServerPassword);
            SqlCommand cmd = new SqlCommand("SELECT A.table_name, column_name FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B WHERE  B.Constraint_Name = A.Constraint_Name AND B.Table_Name = A.Table_Name AND Constraint_Type = 'PRIMARY KEY' AND A.table_name='" + tableName + "'", con);
            cmd.CommandType = CommandType.Text;
            DataTable dtPrKey = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dtPrKey);
            return dtPrKey;
        }

        private static string GetRefColumnNameField(string refTableName, string refCol)
        {
            XDocument mappingConfig = new XDocument();
            mappingConfig = XDocument.Load(@"App.config");
            List<XElement> tables = mappingConfig.Element("configuration").Elements("RefTable").ToList();
            XElement specificTables = tables.Where(ele => ele.Attribute("Name").Value.Equals(refTableName)).SingleOrDefault();
            if (specificTables != null)
                return specificTables.Attribute("NameField").Value;
            else
            {
                string nameField = string.Empty;
                SqlConnection con = new SqlConnection("Server=" + CodeAttribute.ServerIP + "; Database=" + AssemblyInfo.SelectedDatabase + "; uid=" + CodeAttribute.ServerUserName + "; pwd=" + CodeAttribute.ServerPassword);
                SqlCommand cmd = new SqlCommand("select column_name, column_default, is_nullable, data_type from INFORMATION_SCHEMA.COLUMNS where table_name='" + refTableName + "'", con);
                cmd.CommandType = CommandType.Text;
                DataTable dtRefTable = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dtRefTable);
                for (int i = 0; i < dtRefTable.Rows.Count - 1; i++)
                {
                    if (dtRefTable.Rows[i]["column_name"].ToString().Equals(refCol))
                        nameField = dtRefTable.Rows[i + 1]["column_name"].ToString();
                }
                return nameField;
            }
        }
        
        public static string GetDataType(string dataType)
        {

            switch (dataType.ToUpper())
            {
                case "VARCHAR":
                case "NVARCHAR":


                    dataType = "string";

                    break;

                case "BIGINT":


                    dataType = "long";
                    break;

                case "INT":

                    dataType = "int";
                    break;

                case "DECIMAL":


                    dataType = "decimal";

                    break;

                case "BIT":


                    dataType = "bool";

                    break;

                case "DATETIME":
                    dataType = "DateTime";
                    break;

            }
            return dataType;

        }

        public static DataTable GetDBTables(string server, string userName, string Password)
        {
            CodeAttribute.ServerIP = server;
            CodeAttribute.ServerUserName = userName;
            CodeAttribute.ServerPassword = Password;
            SqlConnection cn = new SqlConnection("Server=" + server + "; Database=" + AssemblyInfo.SelectedDatabase + "; uid=" + userName + "; pwd=" + Password);
            cn.Open();
            DataTable tables = cn.GetSchema("Tables");
            return tables;
        }
    }

    public class FieldStructure
    {

        public string Field { get; set; }
        public string DataType { get; set; }
        public bool NullAble { get; set; }
        public string ConstraintType { get; set; }
        public string RefTableName { get; set; }
        public string RefColumnName { get; set; }
        public string RefColumnNameField { get; set; }
        public int MaxLength { get; set; }
        public bool IsIdentity { get; set; }
    }
}
